﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DCS.Domain;
using DCS.Domain.Entities;
using Dcs.Infrastructure;
using DCS.Service;


namespace DCS.SqlProvider
{
    /// <summary>
    /// Класс для взаимодйствия с базой данных MS SQL.
    /// </summary>
    public class SqlDataRepository
    {
        #region Private fields

        // Подключение к БД    
        private readonly string connectionString;
        private readonly Repository repository;

        #endregion

        #region Constructor

        /// <summary>
        /// Конструктор для создания провайдера SQL запросов.
        /// </summary>
        /// <param name="sqlConnectionString"> Строка подключения к БД MS SQL. </param>
        public SqlDataRepository(string sqlConnectionString)
        {
            connectionString = sqlConnectionString;
            repository = new Repository(sqlConnectionString);
        }

        #endregion

        #region Client registration queries

        /// <summary>
        /// Считывает информаци из БД о клиенте по его Guid.
        /// </summary>
        /// <param name="clientGuid"> Guid клиента. </param>
        /// <returns> Число записей имеющихся в БД. </returns>
        public IList<ComputationalClient> SqlGetClientInfo(Guid clientGuid)
        {
            return repository.GetClients(clientGuid);
        }

        ///<summary>
        ///</summary>
        ///<returns></returns>
        public HardwareDescription GetSummaryHardwareCharacteristics()
        {
            var summary = new HardwareDescription();

            var dataset = new DataTable();

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Создаём SQL-команду
                var selectClient =
                    new SqlCommand("SELECT CpuFrequency, CoreCount, AvailableMemory, AvailableRam FROM Clients",
                                   connection);

                // Выполняем запрос            
                var adapter = new SqlDataAdapter(selectClient);

                adapter.Fill(dataset);
                try
                {
                    for (int i = 0; i < dataset.Rows.Count; i++)
                    {
                        summary.CoreCount += Convert.ToInt32(dataset.Rows[i]["CoreCount"]);
                        summary.CpuFrequency += Convert.ToSingle(dataset.Rows[i]["CpuFrequency"]);
                        summary.AvailableMemory += Convert.ToInt32(dataset.Rows[i]["AvailableMemory"]);
                        summary.Ram += Convert.ToInt32(dataset.Rows[i]["AvailableRam"]);
                    }
                }
                catch
                {
                }
            }

            return summary;
        }

        /// <summary>
        /// Добавляет запись о клиенте в БД сервера.
        /// </summary>
        /// <param name="clientGuid"> Guid клиента. </param>
        /// <param name="hardwareDescription"> Характеристики клиента. </param>
        /// <returns> Число добавленных строк. </returns>
        public int SqlAddClient(Guid clientGuid, HardwareDescription hardwareDescription)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Создаём SQL-команду
                var addClientCommand =
                    new SqlCommand(
                        "INSERT INTO Clients (ClientId, CpuFrequency, CoreCount, AvailableMemory, AvailableRam) VALUES (@GUID, @CpuFrequency, @CoreCount, @AvailableMemory, @AvailableRam)",
                        connection);

                // Передаём параметры в SQL-запрос
                addClientCommand.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier).Value = clientGuid;
                addClientCommand.Parameters.Add("@CpuFrequency", SqlDbType.Float).Value =
                    hardwareDescription.CpuFrequency;
                addClientCommand.Parameters.Add("@CoreCount", SqlDbType.SmallInt).Value =
                    hardwareDescription.CoreCount;
                addClientCommand.Parameters.Add("@AvailableMemory", SqlDbType.SmallInt).Value =
                    hardwareDescription.AvailableMemory;
                addClientCommand.Parameters.Add("@AvailableRam", SqlDbType.Int).Value = hardwareDescription.Ram;

                // Выполняем запрос
                return addClientCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Обновляет характеристики клиента в БД.
        /// </summary>
        /// <param name="clientId"> Guid клиента, данные которого обновляются. </param>
        /// <param name="hardwareDescription"> Характеристики клиента. </param>
        /// <returns> Число обработанных строк. </returns>
        public int SqlUpdateClientCharacteristics(Guid clientId, HardwareDescription hardwareDescription)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Создаём SQL-запрос
                var updateClient =
                    new SqlCommand(
                        "UPDATE Clients SET CpuFrequency = @CpuFrequency, CoreCount = @CoreCount, AvailableMemory = @AvailableMemory, AvailableRam = @AvailableRam WHERE ClientId = @clientGuid",
                        connection);

                // Передаём параметры в SQL-запрос
                updateClient.Parameters.Add("@CpuFrequency", SqlDbType.Float).Value = hardwareDescription.CpuFrequency;
                updateClient.Parameters.Add("@CoreCount", SqlDbType.SmallInt).Value = hardwareDescription.CoreCount;
                updateClient.Parameters.Add("@AvailableMemory", SqlDbType.SmallInt).Value =
                    hardwareDescription.AvailableMemory;
                updateClient.Parameters.Add("@AvailableRam", SqlDbType.Int).Value = hardwareDescription.Ram;
                updateClient.Parameters.Add("@clientGuid", SqlDbType.UniqueIdentifier).Value = clientId;

                // Выполняем запрос
                return updateClient.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Устанавливает задание, посчитанное клиентом.
        /// </summary>
        /// <param name="clientId"> Guid клиента, выполнившего расчёт. </param>
        /// <param name="lastTaskId"> Guid задания, посчитанного клиентом. </param>
        public void SqlSetLastCountedTask(Guid clientId, Guid lastTaskId)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Создаём SQL-запрос
                var setLastCountedTask =
                    new SqlCommand(
                        "UPDATE dbo.Clients SET LastTask = @LastTask, LastDate = @LastDate WHERE ClientId = @ClientId",
                        connection);

                // Передаём параметры в SQL-запрос
                setLastCountedTask.Parameters.Add("@LastTask", SqlDbType.UniqueIdentifier).Value = lastTaskId;
                setLastCountedTask.Parameters.Add("@LastDate", SqlDbType.DateTime).Value = DateTime.Now;
                setLastCountedTask.Parameters.Add("@ClientId", SqlDbType.UniqueIdentifier).Value = clientId;

                // Выполнить запрос
                setLastCountedTask.ExecuteNonQuery();
            }
        }

        #endregion

        /// <summary>
        /// Возвращает состояние задания.
        /// </summary>
        /// <param name="taskId"> Guid задания. </param>
        /// <returns> Состояние задания. </returns>
        public string SqlGetTaskState(Guid taskId)
        {
            var taskDataTable = new DataTable();

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var getStateCommand = new SqlCommand("SELECT State FROM Tasks WHERE TaskId = @TaskId");

                var dataAdapter = new SqlDataAdapter(getStateCommand.CommandText, connection);

                dataAdapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;

                // Заполняем DataTable извлечёнными данными
                dataAdapter.Fill(taskDataTable);
            }

            return Convert.ToString(taskDataTable.Rows[0]["State"]);
        }

        /// <summary>
        /// Возвращает результат расчёта.
        /// </summary>
        /// <param name="taskId"> Guid задания. </param>
        /// <returns> Объект TaskResultStruct. </returns>      
        public TaskResult GetTaskResult(Guid taskId)
        {
            var taskResult = new TaskResult { TaskGuid = taskId };
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    var resultDataTable = new DataTable();

                    var getPortionsNumber =
                        new SqlCommand(
                            "SELECT PortionsNumber, CreationDate, CompleteDate FROM Tasks WHERE TaskId = @TaskId",
                            connection);

                    var dataAdapter = new SqlDataAdapter(getPortionsNumber.CommandText, connectionString);
                    dataAdapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;

                    dataAdapter.Fill(resultDataTable);

                    int portionsNumber = Convert.ToInt32(resultDataTable.Rows[0]["PortionsNumber"]);

                    taskResult.CompleteDate = (DateTime)resultDataTable.Rows[0]["CompleteDate"];
                    taskResult.CreationDate = (DateTime)resultDataTable.Rows[0]["CreationDate"];

                    taskResult.PartialResults = new TaskPortionResult[portionsNumber];

                    // Просматриваем все порции
                    for (int i = 0; i < portionsNumber; i++)
                    {
                        taskResult.PartialResults[i] = new TaskPortionResult();

                        resultDataTable = new DataTable();

                        var getPortionResultCommand = new SqlCommand("SELECT FileName, FileData " +
                                                                     "FROM PartialResults WHERE (TaskId = @TaskId) and (PortionNumber = @PortionNumber)",
                                                                     connection);

                        dataAdapter = new SqlDataAdapter(getPortionResultCommand.CommandText, connectionString);

                        dataAdapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;
                        dataAdapter.SelectCommand.Parameters.Add("@PortionNumber", SqlDbType.Int).Value = i;

                        // Заполняем DataTable извлечёнными данными
                        dataAdapter.Fill(resultDataTable);

                        int filesNumber = resultDataTable.Rows.Count;

                        taskResult.PartialResults[i].ResultFiles = new ResultFile[filesNumber];

                        for (int j = 0; j < filesNumber; j++)
                        {
                            taskResult.PartialResults[i].ResultFiles[j] = new ResultFile
                                                                              {
                                                                                  FileName =
                                                                                      resultDataTable.Rows[j][
                                                                                          "FileName"].ToString(),
                                                                                  FileData =
                                                                                      (byte[])
                                                                                      resultDataTable.Rows[j][
                                                                                          "FileData"]
                                                                              };
                        }
                    }
                }
            }
            catch (Exception exception)
            {

            }
            return taskResult;
        }


        /// <summary>
        /// Формирует таблицу порций задачи.
        /// Создаётся запись о каждой порции для контроля её обработки.
        /// </summary>
        /// <param name="taskGuid"> GUID задания, которому принадежат порции. </param>
        /// 
        /// <param name="portionsNumber"> Общее число создаваемых порций. </param>
        private void FormPortionsTable(Guid taskGuid, uint portionsNumber)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                for (int i = 0; i < portionsNumber; i++)
                {
                    var formPortionsCommand =
                        new SqlCommand("INSERT INTO dbo.TaskPortions (TaskId, PortionNumber, State)" +
                                       " VALUES (@TaskId, @PortionNumber, @IsBusy)", connection);

                    formPortionsCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskGuid;
                    formPortionsCommand.Parameters.Add("@PortionNumber", SqlDbType.Int).Value = i;
                    formPortionsCommand.Parameters.Add("@IsBusy", SqlDbType.Bit).Value = 0;

                    formPortionsCommand.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Получает данные о наиболее приоритетном задании, имеющемся в БД на данный момент.
        /// </summary>
        /// <returns> Объект DataTable, содержащий информацию о найденном задании </returns>
        private DataTable GetForegroundTask(out int dependenciesNumber)
        {
            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = new DataTable();

            int state = 0;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Формируем страшный запрос для поиска id задач с max приоритетом, необработанными порциями
                var getTaskIDsCommand =
                    new SqlCommand("SELECT Tasks.TaskID FROM Tasks, TaskPortions " +
                                   "WHERE TaskPortions.TaskId = Tasks.TaskId and " +
                                   "Tasks.State != 'Completed' and " +
                                   "(Tasks.Priority IN (SELECT MAX(Priority) FROM Tasks WHERE Tasks.State != 'Completed')) and " +
                                   "(TaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM TaskPortions WHERE State = 0)) ",
                                   connection);

                // Создаём объект DataAdapter для чтения бинарных данных из базы
                var dataAdapter = new SqlDataAdapter(getTaskIDsCommand.CommandText, connectionString);

                // Заполняем DataTable извлечёнными данными
                dataAdapter.Fill(taskDataTable);

                try
                {
                    if (taskDataTable.Rows.Count == 0)
                    {
                        getTaskIDsCommand =
                            new SqlCommand("SELECT Tasks.TaskID FROM Tasks, TaskPortions " +
                                           "WHERE TaskPortions.TaskId = Tasks.TaskId and " +
                                           "Tasks.State != 'Completed' and " +
                                           "(Tasks.Priority IN (SELECT MAX(Priority) FROM Tasks WHERE Tasks.State != 'Completed')) and " +
                                           "(TaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM TaskPortions WHERE State = 1)) ",
                                           connection);

                        // Создаём объект DataAdapter для чтения бинарных данных из базы
                        dataAdapter = new SqlDataAdapter(getTaskIDsCommand.CommandText, connectionString);

                        // Заполняем DataTable извлечёнными данными
                        dataAdapter.Fill(taskDataTable);

                        state = 1;
                    }
                }
                catch
                {
                }

                try
                {
                    // На сервисе нет заданий
                    if (taskDataTable.Rows.Count == 0)
                    {
                        dependenciesNumber = 0;
                        return null;
                    }
                }
                catch
                {
                }

                try
                {
                    var foregroundTaskGuid = new Guid(taskDataTable.Rows[0]["TaskId"].ToString());

                    taskDataTable.Clear();
                    dependenciesNumber = SqlGetTaskDependencies(foregroundTaskGuid) == null ? 0 : SqlGetTaskDependencies(foregroundTaskGuid).Length;


                    SqlCommand getTaskData;

                    if (dependenciesNumber == 0)
                    {
                        getTaskData
                            =
                            new SqlCommand(
                                "SELECT Tasks.TaskID, Tasks.TaskName, PortionsNumber, ComputationModuleName, " +
                                "ComputationModuleData, PortionNumber, Priority, Summary, AdditionalArguments, DeadLine " +
                                "FROM Tasks, TaskPortions " +
                                "WHERE Tasks.TaskId = @TaskId and TaskPortions.TaskId = @TaskId " +
                                "and (TaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM TaskPortions WHERE State = @State and TaskId = @TaskId))");
                    }
                    else
                    {
                        getTaskData
                            =
                            new SqlCommand(
                                "SELECT Tasks.TaskID, Tasks.TaskName, Tasks.PortionsNumber, Tasks.ComputationModuleName, " +
                                "Tasks.ComputationModuleData, TaskPortions.PortionNumber, Tasks.Priority, Tasks.Summary, AdditionalArguments, TaskDependencies.DependencyName, TaskDependencies.DependencyData, Tasks.DeadLine " +
                                "FROM Tasks, TaskDependencies, TaskPortions " +
                                "WHERE Tasks.TaskId = @TaskId and TaskDependencies.TaskId = @TaskId and TaskPortions.TaskId = @TaskId " +
                                "and (TaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM TaskPortions WHERE State = @State and TaskId = @TaskId))");
                    }
                    // Создаём объект DataAdapter для чтения бинарных данных из базы
                    dataAdapter = new SqlDataAdapter(getTaskData.CommandText, connectionString);

                    dataAdapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value =
                        foregroundTaskGuid;
                    dataAdapter.SelectCommand.Parameters.Add("@State", SqlDbType.Int).Value = state;

                    // Заполняем DataTable извлечёнными данными
                    dataAdapter.Fill(taskDataTable);

                    var markPortionActive =
                        new SqlCommand(
                            "UPDATE TaskPortions SET State = 1 WHERE TaskId = @TaskId and PortionNumber = @PortionNumber",
                            connection);

                    markPortionActive.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = foregroundTaskGuid;
                    markPortionActive.Parameters.Add("@PortionNumber", SqlDbType.Int).Value =
                        Convert.ToInt32(taskDataTable.Rows[0]["PortionNumber"]);

                    markPortionActive.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    dependenciesNumber = 0;
                    return null;
                }
            }

            return taskDataTable;
        }


        /// <summary>
        /// Получает данные о наиболее приоритетном задании, имеющемся в БД на данный момент.
        /// </summary>
        /// <returns> Объект DataTable, содержащий информацию о найденном задании </returns>
        private DataTable GetForegroundTasksPack(out int dependenciesNumber, out List<uint> portionNumbers)
        {
            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = new DataTable();

            var portionsTable = new DataTable();
            portionNumbers = new List<uint>();

            int state = 0;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Формируем страшный запрос для поиска id задач с max приоритетом, необработанными порциями
                var getTaskIDsCommand =
                    new SqlCommand("SELECT Tasks.TaskID FROM Tasks, TaskPortions " +
                                   "WHERE TaskPortions.TaskId = Tasks.TaskId and " +
                                   "Tasks.State != 'Completed' and " +
                                   "(Tasks.Priority IN (SELECT MAX(Priority) FROM Tasks WHERE Tasks.State != 'Completed')) and " +
                                   "(TaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM TaskPortions WHERE State = 0)) ",
                                   connection);

                // Создаём объект DataAdapter для чтения бинарных данных из базы
                var dataAdapter = new SqlDataAdapter(getTaskIDsCommand.CommandText, connectionString);

                // Заполняем DataTable извлечёнными данными
                dataAdapter.Fill(taskDataTable);

                try
                {
                    if (taskDataTable.Rows.Count == 0)
                    {
                        getTaskIDsCommand =
                            new SqlCommand("SELECT Tasks.TaskID FROM Tasks, TaskPortions " +
                                           "WHERE TaskPortions.TaskId = Tasks.TaskId and " +
                                           "Tasks.State != 'Completed' and " +
                                           "(Tasks.Priority IN (SELECT MAX(Priority) FROM Tasks WHERE Tasks.State != 'Completed')) and " +
                                           "(TaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM TaskPortions WHERE State = 1)) ",
                                           connection);

                        // Создаём объект DataAdapter для чтения бинарных данных из базы
                        dataAdapter = new SqlDataAdapter(getTaskIDsCommand.CommandText, connectionString);

                        // Заполняем DataTable извлечёнными данными
                        dataAdapter.Fill(taskDataTable);

                        state = 1;
                    }
                }
                catch
                {
                }

                try
                {
                    // На сервисе нет заданий
                    if (taskDataTable.Rows.Count == 0)
                    {
                        dependenciesNumber = 0;
                        return null;
                    }
                }
                catch
                {
                }

                try
                {
                    var foregroundTaskGuid = new Guid(taskDataTable.Rows[0]["TaskId"].ToString());

                    taskDataTable.Clear();
                    dependenciesNumber = SqlGetTaskDependencies(foregroundTaskGuid) == null ? 0 : SqlGetTaskDependencies(foregroundTaskGuid).Length;


                    SqlCommand getTaskData;

                    if (dependenciesNumber == 0)
                    {
                        getTaskData
                            =
                            new SqlCommand(
                                "SELECT Tasks.TaskID, Tasks.TaskName, PortionsNumber, ComputationModuleName, " +
                                "ComputationModuleData, PortionNumber, Priority, Summary, AdditionalArguments, DeadLine " +
                                "FROM Tasks, TaskPortions " +
                                "WHERE Tasks.TaskId = @TaskId and TaskPortions.TaskId = @TaskId " +
                                "and (TaskPortions.PortionNumber IN (SELECT TOP 10 PortionNumber FROM TaskPortions WHERE State = @State and TaskId = @TaskId ORDER BY PortionNumber))");
                    }
                    else
                    {
                        getTaskData
                            =
                            new SqlCommand(
                                "SELECT Tasks.TaskID, Tasks.TaskName, Tasks.PortionsNumber, Tasks.ComputationModuleName, " +
                                "Tasks.ComputationModuleData, TaskPortions.PortionNumber, Tasks.Priority, Tasks.Summary, AdditionalArguments, TaskDependencies.DependencyName, TaskDependencies.DependencyData, Tasks.DeadLine " +
                                "FROM Tasks, TaskDependencies, TaskPortions " +
                                "WHERE Tasks.TaskId = @TaskId and TaskDependencies.TaskId = @TaskId and TaskPortions.TaskId = @TaskId " +
                                "and (TaskPortions.PortionNumber IN (SELECT TOP 10 PortionNumber FROM TaskPortions WHERE State = @State and TaskId = @TaskId ORDER BY PortionNumber))");
                    }
                    // Создаём объект DataAdapter для чтения бинарных данных из базы
                    dataAdapter = new SqlDataAdapter(getTaskData.CommandText, connectionString);

                    dataAdapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value =
                        foregroundTaskGuid;
                    dataAdapter.SelectCommand.Parameters.Add("@State", SqlDbType.Int).Value = state;

                    // Заполняем DataTable извлечёнными данными
                    dataAdapter.Fill(taskDataTable);


                    var selectedPortions =
                        new SqlCommand(
                            "SELECT TOP 10 PortionNumber FROM TaskPortions WHERE State = @State and TaskId = @TaskId ORDER BY PortionNumber");
                    // Создаём объект DataAdapter для чтения бинарных данных из базы
                    var adapter = new SqlDataAdapter(selectedPortions.CommandText, connectionString);

                    adapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value =
                        foregroundTaskGuid;
                    adapter.SelectCommand.Parameters.Add("@State", SqlDbType.Int).Value = state;
                    adapter.Fill(portionsTable);

                    for (int i = 0; i < portionsTable.Rows.Count; i++)
                    {
                        portionNumbers.Add(Convert.ToUInt32(portionsTable.Rows[i]["PortionNumber"].ToString()));
                    }

                    var markPortionActive =
                        new SqlCommand(
                            "UPDATE TaskPortions SET State = 1 WHERE TaskId = @TaskId and PortionNumber IN " +
                            "(SELECT TOP 10 PortionNumber FROM TaskPortions WHERE (State = @State and TaskId = @TaskId) ORDER BY PortionNumber)",
                            connection);

                    markPortionActive.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = foregroundTaskGuid;
                    markPortionActive.Parameters.Add("@State", SqlDbType.Int).Value = state;

                    markPortionActive.ExecuteNonQuery();
                }
                catch (Exception exc)
                {
                    dependenciesNumber = 0;
                    return null;
                }
            }

            return taskDataTable;
        }

        //private DataTable GetParticularTask()
        //{
        //    // Объект DataTable для хранения массивов зависимостей
        //    DataTable TaskDataTable = new DataTable();

        //    // Формируем команду для извлечения зависимостей
        //    SqlCommand GetTaskCommand = new SqlCommand("SELECT Tasks.TaskID, Tasks.TaskName " +
        //        "ComputationModuleData, PortionsNumber, Priority, DependencyName, DependencyData FROM Tasks, TaskDependencies WHERE Tasks.TaskId = TaskDependencies.TaskId and (Tasks.Priority IN (SELECT MAX(Priority) FROM Tasks))");

        //    // Создаём объект DataAdapter для чтения бинарных данных из базы
        //    SqlDataAdapter DataAdapter = new SqlDataAdapter(GetTaskCommand.CommandText, _connectionString);

        //    // Заполняем DataTable извлечёнными данными
        //    DataAdapter.Fill(TaskDataTable);

        //    return TaskDataTable;
        //}

        /// <summary>
        /// Возвращает текущее состояние сервиса.
        /// </summary>
        /// <returns> Объект ServiceState. </returns>
        public ServiceState SqlGetCurrentServiceState()
        {
            var currentState = new ServiceState();

            var taskNames = new DataTable();

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var selectTasks = new SqlCommand("SELECT (TaskName) FROM Tasks", connection);

                // Создаём объект DataAdapter для чтения бинарных данных из базы
                var dataAdapter = new SqlDataAdapter(selectTasks.CommandText, connectionString);

                dataAdapter.Fill(taskNames);

                int tasksNumber = taskNames.Rows.Count;

                currentState.TasksStates = new TaskState[tasksNumber];

                for (int i = 0; i < tasksNumber; i++)
                {
                    currentState.TasksStates[i] = new TaskState();
                }
            }

            return currentState;
        }

        #region Auxiliary SQL Statements

        /// <summary>
        /// Проверяет существуют ли данные в таблице.
        /// </summary>
        /// <returns> Число записей с указанными данными в таблице. </returns>
        private bool TaskExists(Guid taskId)
        {
            var table = new DataTable();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Создаём SQL-команду
                var selectData =
                    new SqlCommand("SELECT COUNT(*) as TasksCount FROM Tasks WHERE (TaskId = @taskId)",
                                   connection);
                selectData.Parameters.Add("TaskId", SqlDbType.UniqueIdentifier).Value = taskId;

                // Выполняем запрос            
                var adapter = new SqlDataAdapter(selectData);

                adapter.Fill(table);
            }
            return Convert.ToInt32(table.Rows[0]["TasksCount"]) > 0;
        }


        ///<summary>
        ///</summary>
        ///<returns></returns>
        public int SqlClearAll()
        {
            int res = 0;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var clearCommand = new SqlCommand("DELETE FROM TaskPortions", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM PartialResults", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM TaskDependencies", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM Tasks", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM Clients", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM ClientsTasks", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM ClientsWork", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM ServiceState", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM RetransferredTaskDependencies", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM RetransferredTasks", connection);

                res += clearCommand.ExecuteNonQuery();

                clearCommand = new SqlCommand("DELETE FROM RetransferredTaskPortions", connection);

                res += clearCommand.ExecuteNonQuery();
            }

            return res;
        }

        ///<summary>
        ///</summary>
        ///<param name="table"></param>
        ///<param name="fieldToGet"></param>
        ///<param name="conditionalField"></param>
        ///<param name="conditionalValue"></param>
        ///<returns></returns>
        public object SqlGetFieldValue(string table, string fieldToGet, string conditionalField, string conditionalValue)
        {
            try
            {
                var dataset = new DataTable();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    var select =
                        new SqlCommand(
                            "SELECT " + fieldToGet + " FROM " + table + " WHERE " + conditionalField + " = '" +
                            conditionalValue + "'", connection);

                    var adapter = new SqlDataAdapter(select);

                    adapter.Fill(dataset);
                }

                return dataset.Rows[0][fieldToGet];
            }
            catch (Exception)
            {
                return null;
            }
        }

        ///<summary>
        ///</summary>
        ///<param name="taskId"></param>
        ///<returns></returns>
        public int SqlGetTaskProgress(Guid taskId)
        {
            int progress = 0;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var dataset = new DataTable();

                var select = new SqlCommand("SELECT State FROM TaskPortions WHERE TaskId = @TaskId", connection);

                select.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;

                var adapter = new SqlDataAdapter(select);

                adapter.Fill(dataset);

                int portionsNumber = dataset.Rows.Count;

                for (int i = 0; i < portionsNumber; i++)
                {
                    if (Convert.ToInt32(dataset.Rows[i]["State"]) == 2)
                    {
                        progress++;
                    }
                }

                progress *= 100;
                progress /= portionsNumber;
            }

            return progress;
        }

        #endregion

        #region Statistics

        ///<summary>
        ///</summary>
        ///<param name="activityPeriod"></param>
        ///<returns></returns>
        public int SqlGetActiveClientsNumber(DateTime activityPeriod)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var dataset = new DataTable();

                var select = new SqlCommand("SELECT ClientId FROM Clients WHERE LastDate > @minLastDate", connection);

                var adapter = new SqlDataAdapter(select);

                adapter.SelectCommand.Parameters.Add("@minLastDate", SqlDbType.DateTime).Value =
                    activityPeriod.ToString("s");

                adapter.Fill(dataset);

                return dataset.Rows.Count;
            }
        }

        #endregion

        #region Portion

        /// <summary>
        /// Получает задачу для расчёта.
        /// </summary>
        /// <returns></returns>
        public TaskPack GetNextTaskPortion()
        {
            int dependenciesNumber;

            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = GetForegroundTask(out dependenciesNumber);

            // Создаём пакет с задачей

            if (taskDataTable == null)
            {
                return null;
            }

            return DcsConverter.ToTaskPack(taskDataTable, dependenciesNumber); ;
        }

        /// <summary>
        /// Получает задачу для расчёта.
        /// </summary>
        /// <returns></returns>
        public MultiPortionTaskPack GetMultiTaskPortion()
        {
            int dependenciesNumber;
            List<uint> portionNumbers;

            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = GetForegroundTasksPack(out dependenciesNumber, out portionNumbers);

            // Создаём пакет с задачей
            var multiPortionTaskPack = new MultiPortionTaskPack { PortionNumbers = portionNumbers };

            if (taskDataTable == null)
            {
                return null;
            }

            multiPortionTaskPack.Task = DcsConverter.ToTaskPack(taskDataTable, dependenciesNumber);

            return multiPortionTaskPack;
        }


        /// <summary>
        /// Получает "урезанную порцию".
        /// В пакет не включён вычислительный модуль и зависимости.
        /// </summary>
        /// <returns></returns>
        public TaskPack SqlGetLightPortion(Guid task)
        {
            int dependenciesNumber;

            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = GetForegroundTask(out dependenciesNumber);

            // Создаём пакет с задачей
            var newTaskPack = new TaskPack
                                  {
                                      // Создаём зависимости задачи
                                      Dependencies = new Dependency[dependenciesNumber],

                                      // Считываем имя задачи
                                      TaskName = taskDataTable.Rows[0]["TaskName"].ToString(),

                                      Priority = Convert.ToInt16(taskDataTable.Rows[0]["Priority"])
                                  };

            // Считываем имя вычислительного модуля
            newTaskPack.ComputationModule.ModuleName =
                taskDataTable.Rows[0]["ComputationModuleName"].ToString();

            // Считываем вычислительный модуль
            newTaskPack.ComputationModule.ComputationModuleData =
                (byte[])taskDataTable.Rows[0]["ComputationModuleData"];

            // Считываем число порций
            newTaskPack.PortionsNumber =
                Convert.ToUInt32(taskDataTable.Rows[0]["PortionsNumber"].ToString());

            newTaskPack.Summary = taskDataTable.Rows[0]["Summary"].ToString();
            newTaskPack.AdditionalArguments = taskDataTable.Rows[0]["AdditionalArguments"].ToString();

            for (int i = 0; i < dependenciesNumber; i++)
            {
                newTaskPack.Dependencies[i] = new Dependency
                                                  {
                                                      DependencyName =
                                                          taskDataTable.Rows[i]["DependencyName"].ToString(),
                                                      DependencyData =
                                                          (byte[])taskDataTable.Rows[i]["DependencyData"]
                                                  };
            }

            return newTaskPack;
        }

        /// <summary>
        /// Помечает порцию как посчитанную, указывая Guid клиента, выполнившего расчёт.
        /// </summary>
        /// <param name="taskGuid"> Guid задачи частью которой является данная порция. </param>
        /// <param name="portionNumber"> Номер порции. </param>
        /// <param name="calculatedBy"> Guid клиента, выполнившего расчёт. </param>
        public void MarkPortionCounted(Guid taskGuid, uint portionNumber, Guid calculatedBy)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                var markPorion =
                    new SqlCommand(
                        "UPDATE TaskPortions SET State = 2, CalculatedBy = @CalculatedBy WHERE TaskId = @TaskId and PortionNumber = @PortionNumber",
                        connection);

                markPorion.Parameters.Add("CalculatedBy", SqlDbType.UniqueIdentifier).Value = calculatedBy;
                markPorion.Parameters.Add("TaskId", SqlDbType.UniqueIdentifier).Value = taskGuid;
                markPorion.Parameters.Add("PortionNumber", SqlDbType.Int).Value = Convert.ToInt32(portionNumber);

                connection.Open();

                markPorion.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Фиксирует результат расчёта порции в базе данных.
        /// </summary>
        /// <param name="result"> Частичный результат рсчёта. </param>
        public void SetPartialResult(TaskPortionResult result)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Перебираем все файлы
                foreach (var t in result.ResultFiles)
                {
                    try
                    {
                        var setResultPart = new SqlCommand("INSERT INTO dbo.PartialResults " +
                                                           "(TaskId, PortionNumber, FileName, FileData) " +
                                                           "VALUES (@TaskId, @PortionNumber, @FileName, @FileData)",
                                                           connection);

                        setResultPart.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = result.TaskGuid;
                        setResultPart.Parameters.Add("@PortionNumber", SqlDbType.Int).Value =
                            Convert.ToInt32(result.PortionNumber);
                        setResultPart.Parameters.Add("FileName", SqlDbType.NVarChar).Value =
                            t.FileName;
                        setResultPart.Parameters.Add("FileData", SqlDbType.VarBinary).Value =
                            t.FileData;

                        setResultPart.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                    }
                }
            }
        }

        #endregion

        #region Task

        /// <summary>
        /// Добавление задачи на сервер.
        /// </summary>
        /// <param name="taskPack"> Объект TaskPack представляющий задачу. </param>
        /// <returns> Число обрботанных строк в таблице. </returns>
        public int AddTask(TaskPack taskPack)
        {
            int affectedRows = 0;

            SqlCommand addTask;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                if (TaskExists(taskPack.Guid))
                {
                    DeleteTask(taskPack.Guid);
                }

                // Создаём SQL-команду для добавления задания
                addTask =
                    new SqlCommand(
                        "INSERT INTO dbo.Tasks ( TaskId, TaskName, State, ComputationModuleName,  ComputationModuleData, PortionsNumber, Priority, DeadLine, CreationDate, Summary,AdditionalArguments ) " +
                        "VALUES ( @TaskId, @TaskName, @State, @ComputationModuleName, @ComputationModuleData, @PortionsNumber, @Priority, @DeadLine, @CreationDate, @Summary, @AdditionalArguments );",
                        connection);

                // Передаём параметры в SQL-запрос                        
                addTask.Parameters.Add("@TaskName", SqlDbType.NVarChar).Value = taskPack.TaskName;
                addTask.Parameters.Add("@State", SqlDbType.NChar).Value = TaskState.New;
                addTask.Parameters.Add("@ComputationModuleName", SqlDbType.NVarChar).Value = taskPack.ComputationModule.ModuleName;
                addTask.Parameters.Add("@ComputationModuleData", SqlDbType.VarBinary).Value = taskPack.ComputationModule.ComputationModuleData;
                addTask.Parameters.Add("@PortionsNumber", SqlDbType.Int).Value = taskPack.PortionsNumber;
                addTask.Parameters.Add("@Priority", SqlDbType.SmallInt).Value = taskPack.Priority;
                addTask.Parameters.Add("@DeadLine", SqlDbType.DateTime).Value = taskPack.Deadline;
                addTask.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = DateTime.Now;
                addTask.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskPack.Guid;
                addTask.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = taskPack.Summary;
                addTask.Parameters.Add("@AdditionalArguments", SqlDbType.NVarChar).Value = taskPack.AdditionalArguments;

                affectedRows += addTask.ExecuteNonQuery();

                // Добавояем новые зависимрости
                AddTaskDependencies(taskPack);

                // Формируем таблицу порций
                FormPortionsTable(taskPack.Guid, taskPack.PortionsNumber);
            }

            return affectedRows;
        }

        private void DeleteTask(Guid taskId)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var deleteTask = new SqlCommand("DELETE FROM TaskDependencies WHERE TaskId = @TaskId", connection);
                deleteTask.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;
                deleteTask.ExecuteNonQuery();

                deleteTask = new SqlCommand("DELETE FROM TaskPortions WHERE TaskId = @TaskId", connection);
                deleteTask.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;
                deleteTask.ExecuteNonQuery();

                deleteTask = new SqlCommand("DELETE FROM Tasks WHERE TaskId = @TaskId", connection);
                deleteTask.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;
                deleteTask.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Получить зависимости задачи по её GUID.
        /// </summary>
        /// <param name="taskGuid"> GUID задачи. </param>       
        /// <returns>  </returns>
        public Dependency[] SqlGetTaskDependencies(Guid taskGuid)
        {
            // Объект DataTable для хранения массивов зависимостей
            var dependenciesDataTable = new DataTable();

            // Формируем команду для извлечения зависимостей
            var getTaskCommand =
                new SqlCommand("SELECT DependencyName, DependencyData FROM TaskDependencies WHERE TaskId = '" + taskGuid +
                               "'");

            // Создаём объект DataAdapter для чтения бинарных данных из базы
            var dataAdapter = new SqlDataAdapter(getTaskCommand.CommandText, connectionString);

            // Заполняем DataTable извлечёнными данными
            dataAdapter.Fill(dependenciesDataTable);

            // Подсчитываем число найденных записей (по сути - число зависимостей)
            int dependenciesNumber = dependenciesDataTable.Rows.Count;

            if (dependenciesNumber == 0)
            {
                return null;
            }
            // Создайм и заполняем результирующий массив зависимостей
            var dependencies = new Dependency[dependenciesNumber];

            for (int i = 0; i < dependenciesNumber; i++)
            {
                dependencies[i] = new Dependency
                                      {
                                          DependencyData =
                                              (byte[])dependenciesDataTable.Rows[i]["DependencyData"],
                                          DependencyName =
                                              dependenciesDataTable.Rows[i]["DependencyName"].ToString()
                                      };
            }

            return dependencies;
        }

        ///<summary>
        ///</summary>
        ///<param name="taskPack"></param>
        ///<returns></returns>
        public int AddTaskDependencies(TaskPack taskPack)
        {
            int affectedRows = 0;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Добавление всех зависимостей            
                foreach (Dependency t in taskPack.Dependencies)
                {
                    // Создаём SQL-команду для добавления зависимостей                
                    var addDependencies =
                        new SqlCommand(
                            "INSERT INTO dbo.TaskDependencies ( DependencyId, TaskId, DependencyName, DependencyData ) VALUES ( NEWID(), @TaskId, @DependencyName, @DependencyData);",
                            connection);

                    addDependencies.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskPack.Guid;
                    addDependencies.Parameters.Add("@DependencyName", SqlDbType.NVarChar).Value =
                        t.DependencyName;
                    addDependencies.Parameters.Add("@DependencyData", SqlDbType.VarBinary).Value =
                        t.DependencyData;

                    affectedRows += addDependencies.ExecuteNonQuery();
                }
            }
            return affectedRows;
        }

        ///<summary>
        ///</summary>
        ///<param name="client"></param>
        ///<returns></returns>
        public Guid GetLastCountedTask(Guid client)
        {
            var taskDataTable = new DataTable();

            Guid result;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var getStateCommand = new SqlCommand("SELECT LastTask FROM Clients WHERE ClientId = @ClientId");

                var dataAdapter = new SqlDataAdapter(getStateCommand.CommandText, connection);

                dataAdapter.SelectCommand.Parameters.Add("@ClientId", SqlDbType.UniqueIdentifier).Value = client;

                // Заполняем DataTable извлечёнными данными
                dataAdapter.Fill(taskDataTable);

                try
                {
                    result = new Guid(taskDataTable.Rows[0]["LastTask"].ToString());
                }
                catch (Exception)
                {
                    result = Guid.Empty;
                }
            }

            return result;
        }

        ///<summary>
        ///</summary>
        ///<param name="taskId"></param>
        ///<param name="state"></param>
        public void SqlMarkTask(Guid taskId, string state)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    var markCommand = new SqlCommand("Update Tasks SET State = @State, CompleteDate = @CompleteDate WHERE TaskId = @TaskId",
                                                     connection);

                    markCommand.Parameters.Add("@State", SqlDbType.NChar).Value = state;
                    markCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = taskId;
                    markCommand.Parameters.Add("@CompleteDate", SqlDbType.DateTime).Value = DateTime.Now;

                    markCommand.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
            }
        }

        /// <summary>
        /// Возвращает информацию о всех имеющихся на сервисе заданиях.
        /// </summary>
        /// <returns> Массив структур TaskInfo. </returns>
        public TaskInfo[] SqlGetAllTasksInfo()
        {
            try
            {
                var dataset = new DataTable();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    var select = new SqlCommand("SELECT TaskId, TaskName, State, Deadline, Priority FROM dbo.Tasks",
                                                connection);

                    var adapter = new SqlDataAdapter(select);

                    adapter.Fill(dataset);
                }

                var info = new TaskInfo[dataset.Rows.Count];

                for (int i = 0; i < dataset.Rows.Count; i++)
                {
                    info[i] = new TaskInfo
                                  {
                                      Id = new Guid(dataset.Rows[i]["TaskId"].ToString()),
                                      Name = dataset.Rows[i]["TaskName"].ToString(),
                                      State = dataset.Rows[i]["State"].ToString().Trim(),
                                      Deadline = Convert.ToDateTime(dataset.Rows[i]["Deadline"].ToString()),
                                      Priority =
                                          TaskPriority.Priorities[Convert.ToInt16(dataset.Rows[i]["Priority"])]
                                  };

                    info[i].Progress = SqlGetTaskProgress(info[i].Id);
                }

                return info;
            }
            catch (Exception exception)
            {
                return null;
            }
        }

        #endregion

        ///<summary>
        /// Возвращает общее количество необработанны порций.
        ///</summary>
        ///<returns>Количество необработанных порций.</returns>

        public int GetUnprocessedPortionsNumber()
        {
            var taskDataTable = new DataTable();
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    var getStateCommand =
                        new SqlCommand("SELECT COUNT(*) as PortionsCount FROM TaskPortions WHERE State <> 2");
                    var dataAdapter = new SqlDataAdapter(getStateCommand.CommandText, connection);

                    // Заполняем DataTable извлечёнными данными
                    dataAdapter.Fill(taskDataTable);
                }
            }
            catch (Exception exc)
            {
            }
            return Convert.ToInt32(taskDataTable.Rows[0]["PortionsCount"]);
        }

        public TaskPack GetRetransferredTask()
        {
            int dependenciesNumber;

            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = GetForegroundRetransferredTask(out dependenciesNumber);

            if (taskDataTable == null)
            {
                return null;
            }
            var task = DcsConverter.ToTaskPack(taskDataTable, dependenciesNumber);
            task.IsRetransferred = true;
            return task;
        }

        /// <summary>
        /// Получает данные о наиболее приоритетном задании, имеющемся в БД на данный момент.
        /// </summary>
        /// <returns> Объект DataTable, содержащий информацию о найденном задании </returns>
        private DataTable GetForegroundRetransferredTask(out int dependenciesNumber)
        {
            // Объект DataTable для хранения массивов зависимостей
            var taskDataTable = new DataTable();

            int state = 0;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Формируем страшный запрос для поиска id задач с max приоритетом, необработанными порциями
                var getTaskIDsCommand =
                    new SqlCommand(
                        "SELECT RetransferredTasks.TaskID FROM RetransferredTasks, RetransferredTaskPortions " +
                        "WHERE RetransferredTaskPortions.TaskId = RetransferredTasks.TaskId and " +
                        "RetransferredTasks.State != 'Completed' and " +
                        "(RetransferredTasks.Priority IN (SELECT MAX(Priority) FROM RetransferredTasks WHERE RetransferredTasks.State != 'Completed')) and " +
                        "(RetransferredTaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM RetransferredTaskPortions WHERE State = 0)) ",
                        connection);

                // Создаём объект DataAdapter для чтения бинарных данных из базы
                var dataAdapter = new SqlDataAdapter(getTaskIDsCommand.CommandText, connectionString);

                // Заполняем DataTable извлечёнными данными
                dataAdapter.Fill(taskDataTable);

                try
                {
                    if (taskDataTable.Rows.Count == 0)
                    {
                        getTaskIDsCommand =
                            new SqlCommand("SELECT RetransferredTasks.TaskID FROM RetransferredTasks, RetransferredTaskPortions " +
                                           "WHERE RetransferredTaskPortions.TaskId = RetransferredTasks.TaskId and " +
                                           "RetransferredTasks.State != 'Completed' and " +
                                           "(RetransferredTasks.Priority IN (SELECT MAX(Priority) FROM RetransferredTasks WHERE RetransferredTasks.State != 'Completed')) and " +
                                           "(RetransferredTaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM RetransferredTaskPortions WHERE State = 1)) ",
                                           connection);

                        // Создаём объект DataAdapter для чтения бинарных данных из базы
                        dataAdapter = new SqlDataAdapter(getTaskIDsCommand.CommandText, connectionString);

                        // Заполняем DataTable извлечёнными данными
                        dataAdapter.Fill(taskDataTable);

                        state = 1;
                    }
                }
                catch
                {
                }

                try
                {
                    // На сервисе нет заданий
                    if (taskDataTable.Rows.Count == 0)
                    {
                        dependenciesNumber = 0;
                        return null;
                    }
                }
                catch
                {
                }

                try
                {
                    var foregroundTaskGuid = new Guid(taskDataTable.Rows[0]["TaskId"].ToString());

                    taskDataTable.Clear();
                    dependenciesNumber = SqlGetRetransferredTaskDependencies(foregroundTaskGuid) == null ? 0 : SqlGetRetransferredTaskDependencies(foregroundTaskGuid).Length;


                    SqlCommand getTaskData;

                    if (dependenciesNumber == 0)
                    {
                        getTaskData
                            =
                            new SqlCommand(
                                "SELECT RetransferredTasks.TaskID, RetransferredTasks.TaskName, PortionsNumber, ComputationModuleName, " +
                                "ComputationModuleData, PortionNumber, Priority, Summary, AdditionalArguments, DeadLine " +
                                "FROM RetransferredTasks, RetransferredTaskPortions " +
                                "WHERE RetransferredTasks.TaskId = @TaskId and RetransferredTaskPortions.TaskId = @TaskId " +
                                "and (RetransferredTaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM RetransferredTaskPortions WHERE State = @State and TaskId = @TaskId))");
                    }
                    else
                    {
                        getTaskData
                            =
                            new SqlCommand(
                                "SELECT RetransferredTasks.TaskID, RetransferredTasks.TaskName, RetransferredTasks.PortionsNumber, RetransferredTasks.ComputationModuleName, " +
                                "RetransferredTasks.ComputationModuleData, RetransferredTaskPortions.PortionNumber, RetransferredTasks.Priority, RetransferredTasks.Summary, AdditionalArguments, RetransferredTaskDependencies.DependencyName, RetransferredTaskDependencies.DependencyData, RetransferredTasks.DeadLine " +
                                "FROM RetransferredTasks, RetransferredTaskDependencies, RetransferredTaskPortions " +
                                "WHERE RetransferredTasks.TaskId = @TaskId and RetransferredTaskDependencies.TaskId = @TaskId and RetransferredTaskPortions.TaskId = @TaskId " +
                                "and (RetransferredTaskPortions.PortionNumber IN (SELECT MIN(PortionNumber) FROM RetransferredTaskPortions WHERE State = @State and TaskId = @TaskId))");
                    }
                    // Создаём объект DataAdapter для чтения бинарных данных из базы
                    dataAdapter = new SqlDataAdapter(getTaskData.CommandText, connectionString);

                    dataAdapter.SelectCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value =
                        foregroundTaskGuid;
                    dataAdapter.SelectCommand.Parameters.Add("@State", SqlDbType.Int).Value = state;

                    // Заполняем DataTable извлечёнными данными
                    dataAdapter.Fill(taskDataTable);

                    var markPortionActive =
                        new SqlCommand(
                            "UPDATE RetransferredTaskPortions SET State = 1 WHERE TaskId = @TaskId and PortionNumber = @PortionNumber",
                            connection);

                    markPortionActive.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = foregroundTaskGuid;
                    markPortionActive.Parameters.Add("@PortionNumber", SqlDbType.Int).Value =
                        Convert.ToInt32(taskDataTable.Rows[0]["PortionNumber"]);

                    markPortionActive.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    dependenciesNumber = 0;
                    return null;
                }
            }

            return taskDataTable;
        }

        public void AddMultiPortionTaskPack(Service.DCS_Service.MultiPortionTaskPack pack)
        {
            SqlCommand addTask;

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Создаём SQL-команду для добавления задания
                addTask =
                    new SqlCommand(
                        "INSERT INTO dbo.RetransferredTasks ( TaskId, TaskName, State, ComputationModuleName,  ComputationModuleData, PortionsNumber, Priority, DeadLine, CreationDate, Summary,AdditionalArguments ) " +
                        "VALUES ( @TaskId, @TaskName, @State, @ComputationModuleName, @ComputationModuleData, @PortionsNumber, @Priority, @DeadLine, @CreationDate, @Summary, @AdditionalArguments );",
                        connection);

                // Передаём параметры в SQL-запрос                        
                addTask.Parameters.Add("@TaskName", SqlDbType.NVarChar).Value = pack.Task.TaskName;
                addTask.Parameters.Add("@State", SqlDbType.NChar).Value = TaskState.New;
                addTask.Parameters.Add("@ComputationModuleName", SqlDbType.NVarChar).Value = pack.Task.ComputationModule.ModuleName;
                addTask.Parameters.Add("@ComputationModuleData", SqlDbType.VarBinary).Value = pack.Task.ComputationModule.ComputationModuleData;
                addTask.Parameters.Add("@PortionsNumber", SqlDbType.Int).Value = pack.Task.PortionsNumber;
                addTask.Parameters.Add("@Priority", SqlDbType.SmallInt).Value = pack.Task.Priority;
                addTask.Parameters.Add("@DeadLine", SqlDbType.DateTime).Value = pack.Task.Deadline;
                addTask.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = DateTime.Now;
                addTask.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = pack.Task.Guid;
                addTask.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = pack.Task.Summary;
                addTask.Parameters.Add("@AdditionalArguments", SqlDbType.NVarChar).Value = pack.Task.AdditionalArguments;

                addTask.ExecuteNonQuery();

                // Добавояем новые зависимости
                AddRetransferredTaskDependencies(pack.Task);

                // Формируем таблицу порций
                FormRetransferredPortionsTable(pack.Task.Guid, pack.PortionNumbers);
            }
        }

        private void FormRetransferredPortionsTable(Guid guid, uint[] portionsNumber)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                for (int i = 0; i < portionsNumber.Length; i++)
                {
                    var formPortionsCommand =
                        new SqlCommand("INSERT INTO dbo.RetransferredTaskPortions (TaskId, PortionNumber, State)" +
                                       " VALUES (@TaskId, @PortionNumber, @IsBusy)", connection);

                    formPortionsCommand.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = guid;
                    formPortionsCommand.Parameters.Add("@PortionNumber", SqlDbType.Int).Value = portionsNumber[i];
                    formPortionsCommand.Parameters.Add("@IsBusy", SqlDbType.Bit).Value = 0;

                    formPortionsCommand.ExecuteNonQuery();
                }
            }
        }

        private void AddRetransferredTaskDependencies(Service.DCS_Service.TaskPack task)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Добавление всех зависимостей            
                foreach (var dependency in task.Dependencies)
                {
                    // Создаём SQL-команду для добавления зависимостей                
                    var addDependencies =
                        new SqlCommand(
                            "INSERT INTO dbo.RetransferredTaskDependencies ( DependencyId, TaskId, DependencyName, DependencyData ) VALUES ( NEWID(), @TaskId, @DependencyName, @DependencyData);",
                            connection);

                    addDependencies.Parameters.Add("@TaskId", SqlDbType.UniqueIdentifier).Value = task.Guid;
                    addDependencies.Parameters.Add("@DependencyName", SqlDbType.NVarChar).Value =
                        dependency.DependencyName;
                    addDependencies.Parameters.Add("@DependencyData", SqlDbType.VarBinary).Value =
                        dependency.DependencyData;

                    addDependencies.ExecuteNonQuery();
                }
            }
        }

        public Dependency[] SqlGetRetransferredTaskDependencies(Guid taskGuid)
        {
            // Объект DataTable для хранения массивов зависимостей
            var dependenciesDataTable = new DataTable();

            // Формируем команду для извлечения зависимостей
            var getTaskCommand =
                new SqlCommand("SELECT DependencyName, DependencyData FROM RetransferredTaskDependencies WHERE TaskId = '" + taskGuid +
                               "'");

            // Создаём объект DataAdapter для чтения бинарных данных из базы
            var dataAdapter = new SqlDataAdapter(getTaskCommand.CommandText, connectionString);

            // Заполняем DataTable извлечёнными данными
            dataAdapter.Fill(dependenciesDataTable);

            // Подсчитываем число найденных записей (по сути - число зависимостей)
            int dependenciesNumber = dependenciesDataTable.Rows.Count;

            if (dependenciesNumber == 0)
            {
                return null;
            }
            // Создайм и заполняем результирующий массив зависимостей
            var dependencies = new Dependency[dependenciesNumber];

            for (int i = 0; i < dependenciesNumber; i++)
            {
                dependencies[i] = new Dependency
                {
                    DependencyData =
                        (byte[])dependenciesDataTable.Rows[i]["DependencyData"],
                    DependencyName =
                        dependenciesDataTable.Rows[i]["DependencyName"].ToString()
                };
            }

            return dependencies;
        }

        public void MarkRetransferredPortionCounted(Guid taskGuid, uint portionNumber, Guid calculatedBy)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                var markPorion =
                    new SqlCommand(
                        "UPDATE RetransferredTaskPortions SET State = 2, CalculatedBy = @CalculatedBy WHERE TaskId = @TaskId and PortionNumber = @PortionNumber",
                        connection);

                markPorion.Parameters.Add("CalculatedBy", SqlDbType.UniqueIdentifier).Value = calculatedBy;
                markPorion.Parameters.Add("TaskId", SqlDbType.UniqueIdentifier).Value = taskGuid;
                markPorion.Parameters.Add("PortionNumber", SqlDbType.Int).Value = Convert.ToInt32(portionNumber);

                connection.Open();

                markPorion.ExecuteNonQuery();
            }
        }

    }
}